using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using Dapper;

namespace DbFactory
{
    public static class ExpressionExtensions
    {
        public static string ToSqlWhere<T>(this Expression<Func<T, bool>> expression)
        {
            if (expression == null)
                return string.Empty;

            var visitor = new SqlExpressionVisitor();
            visitor.Visit(expression);
            return visitor.WhereClause;
        }

        public static string ToSqlWhere<T, TJoin>(this Expression<Func<T, TJoin, bool>> expression)
        {
            if (expression == null)
                return string.Empty;

            var visitor = new JoinWhereExpressionVisitor();
            visitor.Visit(expression);
            return visitor.WhereClause;
        }

        public static (string SetClause, DynamicParameters Parameters) ToUpdateSet<T>(this Expression<Func<T, T>> setExpression)
        {
            if (setExpression == null)
                throw new ArgumentNullException(nameof(setExpression));

            var visitor = new UpdateSetExpressionVisitor();
            visitor.Visit(setExpression);
            return (visitor.SetClause, visitor.Parameters);
        }

        private class SqlExpressionVisitor : ExpressionVisitor
        {
            public string WhereClause { get; private set; } = string.Empty;

            protected override Expression VisitBinary(BinaryExpression node)
            {
                var left = Visit(node.Left);
                var right = Visit(node.Right);

                string operatorStr = node.NodeType switch
                {
                    ExpressionType.Equal => "=",
                    ExpressionType.NotEqual => "!=",
                    ExpressionType.GreaterThan => ">",
                    ExpressionType.GreaterThanOrEqual => ">=",
                    ExpressionType.LessThan => "<",
                    ExpressionType.LessThanOrEqual => "<=",
                    ExpressionType.AndAlso => "AND",
                    ExpressionType.OrElse => "OR",
                    _ => throw new NotSupportedException($"Operator {node.NodeType} is not supported")
                };

                if (node.NodeType == ExpressionType.AndAlso || node.NodeType == ExpressionType.OrElse)
                {
                    WhereClause = $"({WhereClause} {operatorStr} {right})";
                }
                else
                {
                    WhereClause = $"{left} {operatorStr} {right}";
                }

                return node;
            }

            protected override Expression VisitMethodCall(MethodCallExpression node)
            {
                if (node.Method.DeclaringType == typeof(string))
                {
                    switch (node.Method.Name)
                    {
                        case "Contains":
                            var obj = Visit(node.Object);
                            var arg = Visit(node.Arguments[0]);
                            var argValue = arg.ToString().Trim('\'');
                            WhereClause = $"{obj} LIKE '%{argValue}%'";
                            return node;
                        case "StartsWith":
                            obj = Visit(node.Object);
                            arg = Visit(node.Arguments[0]);
                            argValue = arg.ToString().Trim('\'');
                            WhereClause = $"{obj} LIKE '{argValue}%'";
                            return node;
                        case "EndsWith":
                            obj = Visit(node.Object);
                            arg = Visit(node.Arguments[0]);
                            argValue = arg.ToString().Trim('\'');
                            WhereClause = $"{obj} LIKE '%{argValue}'";
                            return node;
                    }
                }
                else if (node.Method.DeclaringType == typeof(Enumerable))
                {
                    switch (node.Method.Name)
                    {
                        case "Contains":
                            var list = GetListFromExpression(node.Arguments[0]);
                            var member = Visit(node.Arguments[1]);
                            WhereClause = $"{member} IN ({string.Join(",", list)})";
                            return node;
                    }
                }

                throw new NotSupportedException($"Method {node.Method.Name} is not supported");
            }

            protected override Expression VisitMember(MemberExpression node)
            {
                if (node.Expression != null && node.Expression.NodeType == ExpressionType.Parameter)
                {
                    WhereClause = node.Member.Name;
                }
                else if (node.Expression != null)
                {
                    var obj = Visit(node.Expression);
                    WhereClause = $"{obj}.{node.Member.Name}";
                }
                else
                {
                    var value = GetValueFromMemberExpression(node);
                    WhereClause = FormatValue(value);
                }
                return node;
            }

            protected override Expression VisitConstant(ConstantExpression node)
            {
                WhereClause = FormatValue(node.Value);
                return node;
            }

            private string FormatValue(object value)
            {
                if (value == null)
                    return "NULL";
                
                if (value is string str)
                    return $"'{str.Replace("'", "''")}'";
                
                if (value is DateTime dateTime)
                    return $"'{dateTime:yyyy-MM-dd HH:mm:ss}'";
                
                if (value is bool boolean)
                    return boolean ? "1" : "0";
                
                if (value is IEnumerable && !(value is string))
                {
                    var items = ((IEnumerable)value).Cast<object>().Select(FormatValue);
                    return $"({string.Join(",", items)})";
                }

                return value.ToString();
            }

            private object GetValueFromMemberExpression(MemberExpression memberExpression)
            {
                var objectMember = Expression.Convert(memberExpression, typeof(object));
                var getterLambda = Expression.Lambda<Func<object>>(objectMember);
                var getter = getterLambda.Compile();
                return getter();
            }

            private List<string> GetListFromExpression(Expression expression)
            {
                var list = new List<string>();
                if (expression is ConstantExpression constantExpression)
                {
                    if (constantExpression.Value is IEnumerable enumerable)
                    {
                        foreach (var item in enumerable)
                        {
                            list.Add(FormatValue(item));
                        }
                    }
                }
                return list;
            }
        }

        private class JoinWhereExpressionVisitor : ExpressionVisitor
        {
            public string WhereClause { get; private set; } = string.Empty;

            protected override Expression VisitBinary(BinaryExpression node)
            {
                var left = Visit(node.Left);
                var right = Visit(node.Right);

                string operatorStr = node.NodeType switch
                {
                    ExpressionType.Equal => "=",
                    ExpressionType.NotEqual => "!=",
                    ExpressionType.GreaterThan => ">",
                    ExpressionType.GreaterThanOrEqual => ">=",
                    ExpressionType.LessThan => "<",
                    ExpressionType.LessThanOrEqual => "<=",
                    ExpressionType.AndAlso => "AND",
                    ExpressionType.OrElse => "OR",
                    _ => throw new NotSupportedException($"Operator {node.NodeType} is not supported")
                };

                if (node.NodeType == ExpressionType.AndAlso || node.NodeType == ExpressionType.OrElse)
                {
                    WhereClause = $"({WhereClause} {operatorStr} {right})";
                }
                else
                {
                    WhereClause = $"{left} {operatorStr} {right}";
                }

                return node;
            }

            protected override Expression VisitMember(MemberExpression node)
            {
                if (node.Expression != null)
                {
                    var parameterName = node.Expression.ToString();
                    var tableAlias = parameterName == "t" ? "t" : "j";
                    WhereClause = $"{tableAlias}.{node.Member.Name}";
                }
                else
                {
                    var value = GetValueFromMemberExpression(node);
                    WhereClause = FormatValue(value);
                }
                return node;
            }

            protected override Expression VisitConstant(ConstantExpression node)
            {
                WhereClause = FormatValue(node.Value);
                return node;
            }

            private string FormatValue(object value)
            {
                if (value == null)
                    return "NULL";
                
                if (value is string str)
                    return $"'{str.Replace("'", "''")}'";
                
                if (value is DateTime dateTime)
                    return $"'{dateTime:yyyy-MM-dd HH:mm:ss}'";
                
                if (value is bool boolean)
                    return boolean ? "1" : "0";
                
                if (value is IEnumerable && !(value is string))
                {
                    var items = ((IEnumerable)value).Cast<object>().Select(FormatValue);
                    return $"({string.Join(",", items)})";
                }

                return value.ToString();
            }

            private object GetValueFromMemberExpression(MemberExpression memberExpression)
            {
                var objectMember = Expression.Convert(memberExpression, typeof(object));
                var getterLambda = Expression.Lambda<Func<object>>(objectMember);
                var getter = getterLambda.Compile();
                return getter();
            }
        }

        private class UpdateSetExpressionVisitor : ExpressionVisitor
        {
            public string SetClause { get; private set; } = string.Empty;
            public DynamicParameters Parameters { get; } = new DynamicParameters();
            private int _parameterIndex = 0;

            protected override Expression VisitMemberInit(MemberInitExpression node)
            {
                var bindings = new List<string>();
                foreach (var binding in node.Bindings)
                {
                    if (binding is MemberAssignment assignment)
                    {
                        var value = GetValueFromExpression(assignment.Expression);
                        var paramName = $"@p{_parameterIndex++}";
                        Parameters.Add(paramName, value);
                        bindings.Add($"{binding.Member.Name} = {paramName}");
                    }
                }
                SetClause = string.Join(", ", bindings);
                return node;
            }

            private object GetValueFromExpression(Expression expression)
            {
                if (expression is ConstantExpression constantExpression)
                {
                    return constantExpression.Value;
                }
                else if (expression is MemberExpression memberExpression)
                {
                    var objectMember = Expression.Convert(memberExpression, typeof(object));
                    var getterLambda = Expression.Lambda<Func<object>>(objectMember);
                    var getter = getterLambda.Compile();
                    return getter();
                }
                else if (expression is UnaryExpression unaryExpression)
                {
                    return GetValueFromExpression(unaryExpression.Operand);
                }
                throw new NotSupportedException($"Expression type {expression.NodeType} is not supported in update set expression");
            }
        }
    }

    public static class QueryExtensions
    {
        public static string ToJoinSql<T, TJoin>(this Expression<Func<T, TJoin, bool>> expression)
        {
            if (expression == null)
                return string.Empty;

            var visitor = new JoinExpressionVisitor();
            visitor.Visit(expression);
            return visitor.JoinClause;
        }

        private class JoinExpressionVisitor : ExpressionVisitor
        {
            public string JoinClause { get; private set; } = string.Empty;

            protected override Expression VisitBinary(BinaryExpression node)
            {
                var left = Visit(node.Left);
                var right = Visit(node.Right);

                string operatorStr = node.NodeType switch
                {
                    ExpressionType.Equal => "=",
                    ExpressionType.NotEqual => "!=",
                    ExpressionType.GreaterThan => ">",
                    ExpressionType.GreaterThanOrEqual => ">=",
                    ExpressionType.LessThan => "<",
                    ExpressionType.LessThanOrEqual => "<=",
                    ExpressionType.AndAlso => "AND",
                    ExpressionType.OrElse => "OR",
                    _ => throw new NotSupportedException($"Operator {node.NodeType} is not supported")
                };

                JoinClause = $"{left} {operatorStr} {right}";
                return node;
            }

            protected override Expression VisitMember(MemberExpression node)
            {
                if (node.Expression != null)
                {
                    var parameterName = node.Expression.ToString();
                    var tableAlias = parameterName == "t" ? "t" : "j";
                    JoinClause = $"{tableAlias}.{node.Member.Name}";
                }
                return node;
            }
        }
    }
} 